﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Collections;
using System.Data;

/// <summary>
/// SQLUtility 的摘要描述
/// </summary>
namespace org.project.web.utils
{
    public class SQLUtility
    {
        public SQLUtility()
        {
            //
            // TODO: 在此加入建構函式的程式碼
            //
        }

        //傳回資料庫查詢之後得到的紀錄筆數
        public int ExecuteCommandRows(string connString, string CommandString)
        {

            SqlConnection conn = null;
            SqlCommand cmd = null;

            try
            {
                conn = new SqlConnection(connString);
                cmd = new SqlCommand(CommandString, conn);

                conn.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    return reader.FieldCount;
                }
                else
                {
                    return -1;
                }
            }

            finally
            {

                if (cmd != null)
                    cmd.Dispose();

                if (conn != null)
                {
                    conn.Close();
                    conn.Dispose();
                }
            }
        }

        public List<Dictionary<String, Object>> ExecuteCommand(string connString, string CommandString)
        {

            SqlConnection conn = null;
            SqlCommand cmd = null;

            try
            {
                conn = new SqlConnection(connString);
                cmd = new SqlCommand(CommandString, conn);

                conn.Open();
                SqlDataReader reader = cmd.ExecuteReader();

                Dictionary<String, Object> map = new Dictionary<String, Object>();

                List<Dictionary<String, Object>> list = new List<Dictionary<String, Object>>();
                //int row = 0;
                int index = 0;
                //index = reader.FieldCount;
                while (reader.Read())
                {
                    map = new Dictionary<string, object>();
                    map.Add((String)reader.GetName(index), reader.GetValue(index));
                    list.Add(map);
                    index++;
                }
                return list;
            }

            finally
            {

                if (cmd != null)
                    cmd.Dispose();

                if (conn != null)
                {
                    conn.Close();
                    conn.Dispose();
                }
            }
        }

        //用於SQL 命令中只需執行結果，並傳回受影響的資料列筆數
        public int ExecuteCommandNonQuery(string connString, string CommandString)
        {

            SqlConnection conn = null;
            SqlCommand cmd = null;

            try
            {
                conn = new SqlConnection(connString);
                cmd = new SqlCommand(CommandString, conn);
                conn.Open();
                return cmd.ExecuteNonQuery();
            }

            finally
            {

                if (cmd != null)
                    cmd.Dispose();

                if (conn != null)
                {
                    conn.Close();
                    conn.Dispose();
                }
            }
        }

        //用於SQL 命令中只需執行結果，並傳回受影響的資料列筆數
        //有bug ，以後再來處理 使用@或是? 的佔位符號造成的問題 
        /** 
        public int ExecuteCommandNonQuery(string connString, string CommandString, Object[] ParameterValues)
        {

            SqlConnection conn = null;
            SqlCommand cmd = null;

            try
            {

                conn = new SqlConnection(connString);
                cmd = new SqlCommand(CommandString, conn);
                for (int index= 0;index<cmd.Parameters.Count;index++){
                    SqlParameter parameter =  cmd.CreateParameter();
                    //cmd.Parameters[index].Value = ParameterValues[index];

                    cmd.Parameters.Add(cmd.Parameters[0].ParameterName,ParameterValues[index]);

                    //cmd.Parameters.AddWithValue("FirstName", txtFirstName.Text);
                }
                conn.Open();
                return cmd.ExecuteNonQuery();
            }

            finally
            {

                if (cmd != null)
                    cmd.Dispose();

                if (conn != null)
                {
                    conn.Close();
                    conn.Dispose();
                }
            }
        }
        */


        

        /*** 使用 SqlDataAdapter 來執行查詢結果
         * 至於 DataSet 部分，請在指定的位置新增一個DataSet，
         * 再將 DataSet 以 ref DataSet 參照的方式傳入，
         * 最後要跟指定的元件進行 DataSource 的 Bind 時，
         * 就直接使用 DataSet 跟該物件的 DataSource 進行 DataBind()
         * ex (GridView):
          DataSet dataSet = new DataSet();
          SqlDataAdapter adapter = utility.UseSqlDataAdapterExecuteCommand(ConfigurationManager.ConnectionStrings["SQLConnectionString"].ConnectionString, sql,ref dataSet);
          UserGridView.DataSource = dataSet;
          UserGridView.DataBind();
         * 
         */
        public SqlDataAdapter UseSqlDataAdapterExecuteCommand(string connString, string CommandString, ref DataSet dataSet)
        {

            SqlConnection conn = null;
            SqlCommand cmd = null;
            SqlDataAdapter adapter = null;
            try
            {
                conn = new SqlConnection(connString);
                cmd = new SqlCommand(CommandString, conn);

                //conn.Open();
                //SqlDataAdapter 會自行檢查連線狀態
                //SqlDataReader reader = cmd.ExecuteReader();
                adapter = new SqlDataAdapter(CommandString, conn);
                dataSet = new DataSet();
                adapter.Fill(dataSet);
                //return adapter;
            }
                
            catch (Exception e) {
                e.Message.ToString();
            }
            return adapter;
        }

        /* //未測試
        public List<Dictionary<String, Object>> ExecuteCommandResult(string connString, string CommandString)
        {

            SqlConnection conn = null;
            SqlCommand cmd = null;

            try
            {
                conn = new SqlConnection(connString);
                cmd = new SqlCommand(CommandString, conn);

                conn.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                Dictionary<String, Object> map = new Dictionary<String, Object>();

                List<Dictionary<String, Object>> list = new List<Dictionary<String, Object>>();
                int row = 0;
                int index = 0;
                index = reader.FieldCount;
                while (reader.Read())
                {
                    map = new Dictionary<string, object>();
                    map.Add((String)reader.GetName(index), reader.GetValue(index));
                    list.Add(map);
                    index++;
                }
                return list;
            }

            finally
            {

                if (cmd != null)
                    cmd.Dispose();

                if (conn != null)
                {
                    conn.Close();
                    conn.Dispose();
                }
            }
        }
        */

        public void AddParamAndExecuteCommand(string connString, string CommandString, String[] param)
        {

            SqlConnection conn = null;
            SqlCommand cmd = null;

            try
            {
                conn = new SqlConnection(connString);
                cmd = new SqlCommand(CommandString, conn);
                for (int i = 0; i < param.Length; i++)
                {
                    cmd.Parameters[i].Value = param[i];
                }
                conn.Open();

            }

            finally
            {

                if (cmd != null)
                    cmd.Dispose();

                if (conn != null)
                    //conn.Dispose();
                    conn.Close();
            }
        }
    }
}